﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;

namespace AssessmentActions
{
    public class AssessmentDatabaseActions
    {
        private string _dbConnectionString;

        public AssessmentDatabaseActions(string dbConnectionString)
        {
            this._dbConnectionString = dbConnectionString;
        }

        /// <summary>
        /// Gets a test in XML format
        /// </summary>
        /// <param name="testID">Test ID of the test to be retrieved</param>
        /// <param name="withAnswers">True or false if answers are to be included</param>
        /// <returns>Test in XML format</returns>
        public string fetchTestXml(int testID, bool withAnswers)
        {
            return getTest(testID, withAnswers, true, false);
        }

        /// <summary>
        /// Gets a test in HTML format
        /// </summary>
        /// <param name="testID">Test ID of the test to be retrieved</param>
        /// <param name="withAnswers">True or false if answers are to be included</param>
        /// <returns>Test in HTML format</returns>
        public string fetchTestHtml(int testID, bool withAnswers)
        {
            return getTest(testID, withAnswers, false, false);
        }

        public string fetchTestHtmlWithSelectors(int testID)
        {
            return getTest(testID, false, false, true);
        }

        private string getTest(int testID, bool withAnswers, bool isXml, bool isWithSelectors)
        {
            string assessmentString = "";

            // Connect to local database
            SqlConnection myConnection = new SqlConnection(this._dbConnectionString);
            myConnection.Open();

            string serializedAssessment = "";

            // Retrieve assessment information from database
            try
            {
                SqlDataReader myReader = null;
                SqlCommand myCommand = new SqlCommand("SELECT * FROM Assessments WHERE id = '" + testID + "'", myConnection);
                myReader = myCommand.ExecuteReader();
                while (myReader.Read())
                {
                    serializedAssessment = myReader["SerializedAssessment"].ToString();
                }
                myReader.Close();
            }
            catch (Exception e)
            {
                assessmentString = "Invalid Test ID submitted";
                return assessmentString;
            }
            myConnection.Close();

            // Deserialize the assessment
            Assessment assessment = null;
            byte[] assessmentStringBytes = Convert.FromBase64String(serializedAssessment);
            MemoryStream memoryStream = new MemoryStream(assessmentStringBytes);
            BinaryFormatter binaryFormatter = new BinaryFormatter();
            assessment = (Assessment)binaryFormatter.Deserialize(memoryStream);
            memoryStream.Close();

            // Get the final string representation of the assessment
            if (isWithSelectors)
            {
                assessmentString = assessment.getAssessmentHtmlWithSelectors();
            }
            else
            {
                if (isXml)
                {
                    if (withAnswers)
                        assessmentString = assessment.getAssessmentXmlWithAnswers();
                    else
                        assessmentString = assessment.getAssessmentXml();
                }
                else
                {
                    if (withAnswers)
                        assessmentString = assessment.getAssessmentHtmlWithAnswers();
                    else
                        assessmentString = assessment.getAssessmentHtml();
                }
            }
            return assessmentString;
        }

        /// <summary>
        /// Get the assessment from the database
        /// </summary>
        /// <param name="testID">assigned assessment id</param>
        /// <returns>Assessment</returns>
        public Assessment getAssessmentFromId(int testID)
        {
            Assessment assessment = new Assessment();

            // Connect to local database
            SqlConnection myConnection = new SqlConnection(this._dbConnectionString);
            myConnection.Open();

            string serializedAssessment = "";

            // Retrieve assessment information from database
            try
            {
                SqlDataReader myReader = null;
                SqlCommand myCommand = new SqlCommand("SELECT * FROM Assessments WHERE id = '" + testID + "'", myConnection);
                myReader = myCommand.ExecuteReader();
                while (myReader.Read())
                {
                    serializedAssessment = myReader["SerializedAssessment"].ToString();
                }
                myReader.Close();

                // Deserialize the assessment
                byte[] assessmentStringBytes = Convert.FromBase64String(serializedAssessment);
                MemoryStream memoryStream = new MemoryStream(assessmentStringBytes);
                BinaryFormatter binaryFormatter = new BinaryFormatter();
                assessment = (Assessment)binaryFormatter.Deserialize(memoryStream);
                memoryStream.Close();
            }
            catch (Exception e)
            {
            }
            finally
            {
                myConnection.Close();
            }

            return assessment;
        }

                /// <summary>
        /// Get the assessment from the database
        /// </summary>
        /// <param name="testName">assigned assessment id</param>
        /// <returns>Assessment</returns>
        public Assessment getAssessmentFromName(string testName)
        {
            Assessment assessment = new Assessment();

            // Connect to local database
            SqlConnection myConnection = new SqlConnection(this._dbConnectionString);
            myConnection.Open();

            string serializedAssessment = "";

            // Retrieve assessment information from database
            try
            {
                SqlDataReader myReader = null;
                SqlCommand myCommand = new SqlCommand("SELECT * FROM Assessments WHERE Title = '" + testName + "'", myConnection);
                myReader = myCommand.ExecuteReader();
                while (myReader.Read())
                {
                    serializedAssessment = myReader["SerializedAssessment"].ToString();
                }
                myReader.Close();

                // Deserialize the assessment
                byte[] assessmentStringBytes = Convert.FromBase64String(serializedAssessment);
                MemoryStream memoryStream = new MemoryStream(assessmentStringBytes);
                BinaryFormatter binaryFormatter = new BinaryFormatter();
                assessment = (Assessment)binaryFormatter.Deserialize(memoryStream);
                memoryStream.Close();
            }
            catch (Exception e)
            {
            }
            finally
            {
                myConnection.Close();
            }

            return assessment;
        }


        /// <summary>
        /// Create a sample test
        /// </summary>
        /// <param name="subject"></param>
        /// <returns></returns>
        public int createTest(string subject)
        {
            int assessmentDatabaseId = 0;

            // Connect to local database.
            SqlConnection myConnection = new SqlConnection(this._dbConnectionString);
            myConnection.Open();

            // Create the assessment
            Assessment assessment = new Assessment();
            assessment.Id = 1;
            assessment.Name = subject + " Test " + assessment.Id;
            assessment.CreatedDate = DateTime.Now;

            // Get all the True/False questions from the database and add them to the assessment
            try
            {
                SqlDataReader myReader2 = null;
                SqlCommand myCommand2 = new SqlCommand("SELECT [QuestionsTrueOrFalse].* FROM [QuestionsTrueOrFalse] " +
                                "LEFT JOIN [QuestionSubjects] " +
                                "ON [QuestionsTrueOrFalse].[Subject] = [QuestionSubjects].[Id] " +
                                "WHERE [QuestionSubjects].[Name] = '" + subject + "'", myConnection);
                myReader2 = myCommand2.ExecuteReader();
                while (myReader2.Read())
                {
                    int questionId = Int16.Parse(myReader2["Id"].ToString());
                    string text = myReader2["Text"].ToString();
                    string correctAnswerString = myReader2["CorrectAnswer"].ToString();
                    bool correctAnswer = false;
                    if (correctAnswerString == "True")
                        correctAnswer = true;

                    assessment.addQuestion(new TrueFalseQuestion(questionId, text, correctAnswer));
                }
                myReader2.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            // Get all the multiple choice questions from the database and add them to the assessment
            try
            {
                SqlDataReader myReader3 = null;
                SqlCommand myCommand3 = new SqlCommand("SELECT [QuestionsMultipleChoice].* FROM [QuestionsMultipleChoice] " +
                                "LEFT JOIN [QuestionSubjects] " +
                                "ON [QuestionsMultipleChoice].[Subject] = [QuestionSubjects].[Id] " +
                                "WHERE [QuestionSubjects].[Name] = '" + subject + "'", myConnection);
                myReader3 = myCommand3.ExecuteReader();
                while (myReader3.Read())
                {
                    int questionId = Int16.Parse(myReader3["Id"].ToString());
                    string text = myReader3["Text"].ToString();
                    int correctAnswer = Int16.Parse(myReader3["CorrectAnswer"].ToString());
                    List<string> finalAnswers = new List<string>();
                    string combinedAnswers = myReader3["Answers"].ToString();
                    string[] splitAnswers = combinedAnswers.Split('|');
                    foreach (string answer in splitAnswers)
                    {
                        finalAnswers.Add(answer);
                    }

                    assessment.addQuestion(new MultipleChoiceQuestion(questionId, text, finalAnswers, correctAnswer));
                }
                myReader3.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            // Serialize the assessment for storage in the database
            MemoryStream memoryStream = new MemoryStream();
            BinaryFormatter binaryFormatter = new BinaryFormatter();
            binaryFormatter.Serialize(memoryStream, assessment);
            string serializedAssessment = Convert.ToBase64String(memoryStream.ToArray());

            // Insert into the database
            SqlDataReader myReader4 = null;
            SqlCommand myCommand4 = new SqlCommand("INSERT INTO Assessments (Subject, Title, Date, SerializedAssessment) VALUES ('" + subject + "', '" + assessment.Name + "', '" + assessment.CreatedDate + "', '" + serializedAssessment + "') SELECT @@IDENTITY AS AssessmentId", myConnection);
            myReader4 = myCommand4.ExecuteReader();
            while (myReader4.Read())
            {
                assessmentDatabaseId = Int16.Parse(myReader4["AssessmentId"].ToString());

            }
            myReader4.Close();
            myConnection.Close();

            // return the assessment id
            return assessmentDatabaseId;
        }

        /// <summary>
        /// Gets the test numbers from the database for a given subject
        /// </summary>
        /// <param name="subject">Subject as a string.</param>
        /// <returns>Array of int of test numbers</returns>
        public int[] fetchTestNumbers(string subject)
        {
            // connect to local database
            SqlConnection myConnection = new SqlConnection(this._dbConnectionString);
            myConnection.Open();

            List<int> listOfTests = new List<int>();

            // get array from database
            try
            {
                SqlDataReader myReader = null;
                SqlCommand myCommand = new SqlCommand("SELECT * FROM Assessments WHERE Subject = '" + subject + "'", myConnection);
                myReader = myCommand.ExecuteReader();
                while (myReader.Read())
                {
                    listOfTests.Add(Int16.Parse(myReader["id"].ToString()));
                }
                myReader.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            myConnection.Close();

            // return the list
            return listOfTests.ToArray();
        }


        public string[] fetchTestResultsForUser(string UserId, string testName)
        {
            // connect to local database
            SqlConnection myConnection = new SqlConnection(this._dbConnectionString);
            myConnection.Open();

            string returnResults = "";
            List<string> listOfTests = new List<string>();

            // get array from database
            try
            {
                SqlDataReader myReader = null;
                SqlCommand myCommand = new SqlCommand("SELECT * FROM AssessmentGrades WHERE UserId = '" + UserId + "' AND testName = '" + testName + "'", myConnection);
                myReader = myCommand.ExecuteReader();
                while (myReader.Read())
                {
                    returnResults = myReader["Answers"].ToString();
                    listOfTests.Add(myReader["Answers"].ToString());
                }
                myReader.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            myConnection.Close();

            // return the list
            //return returnResults;
            return listOfTests.ToArray();
        }


        /// <summary>
        /// Posts assessment answers to database for a given test and user
        /// </summary>
        /// <param name="testId">Test ID as int</param>
        /// <param name="userId">User ID as int</param>
        /// <param name="answers">String containing answers. Each answer separated with a pipe "|". T or F for 
        /// true/false questions. A zero based int for multiple choice questions.</param>
        /// <returns></returns>
        public bool postTestAnswers(int testId, string userId, string answers, string testName)
        {
            bool successful = false;

            EncryptionService.EncryptionServiceClient esc = new EncryptionService.EncryptionServiceClient();
            string decryptedAnswers = esc.Decrypt(answers, "secretPassword");

            // Connect to local database
            SqlConnection myConnection = new SqlConnection(this._dbConnectionString);
            myConnection.Open();

            // Insert into database
            try
            {
                SqlCommand myCommand = new SqlCommand("INSERT INTO AssessmentGrades (TestId, UserId, Answers, TestName) " +
                        "VALUES ('" + testId + "', '" + userId + "', '" + decryptedAnswers + "', '" + testName + "')", myConnection);
                myCommand.ExecuteNonQuery();
                successful = true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            myConnection.Close();
            return successful;
        }

        /// <summary>
        /// get all true/false questions from the database
        /// </summary>
        /// <returns></returns>
        public List<TrueFalseQuestion> getAllTrueFalseFromDatabase()
        {
            List<TrueFalseQuestion> questions = new List<TrueFalseQuestion>();

            // Connect to local database.
            SqlConnection myConnection = new SqlConnection(this._dbConnectionString);
            myConnection.Open();
            
            // Get all the True/False questions from the database and add them to the assessment
            try
            {
                SqlDataReader myReader2 = null;
                SqlCommand myCommand2 = new SqlCommand("SELECT [QuestionsTrueOrFalse].* FROM [QuestionsTrueOrFalse] ", myConnection);
                myReader2 = myCommand2.ExecuteReader();
                while (myReader2.Read())
                {
                    int questionId = Int16.Parse(myReader2["Id"].ToString());
                    string text = myReader2["Text"].ToString();
                    string correctAnswerString = myReader2["CorrectAnswer"].ToString();
                    bool correctAnswer = false;
                    if (correctAnswerString == "True")
                        correctAnswer = true;

                    questions.Add(new TrueFalseQuestion(questionId, text, correctAnswer));
                }
                myReader2.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                myConnection.Close();
            }



            return questions;
        }

        /// <summary>
        /// get all true/false questions from the database
        /// </summary>
        /// <returns></returns>
        public List<MultipleChoiceQuestion> getAllMultipleChoiceFromDatabase()
        {
            List<MultipleChoiceQuestion> questions = new List<MultipleChoiceQuestion>();

            // Connect to local database.
            SqlConnection myConnection = new SqlConnection(this._dbConnectionString);
            

            // Get all the multiple choice questions from the database and add them to the assessment
            try
            {
                myConnection.Open();

                SqlDataReader myReader3 = null;
                SqlCommand myCommand3 = new SqlCommand("SELECT [QuestionsMultipleChoice].* FROM [QuestionsMultipleChoice] ", myConnection);
                myReader3 = myCommand3.ExecuteReader();
                while (myReader3.Read())
                {
                    int questionId = Int16.Parse(myReader3["Id"].ToString());
                    string text = myReader3["Text"].ToString();
                    int correctAnswer = Int16.Parse(myReader3["CorrectAnswer"].ToString());
                    List<string> finalAnswers = new List<string>();
                    string combinedAnswers = myReader3["Answers"].ToString();
                    string[] splitAnswers = combinedAnswers.Split('|');
                    foreach (string answer in splitAnswers)
                    {
                        finalAnswers.Add(answer);
                    }

                    questions.Add(new MultipleChoiceQuestion(questionId, text, finalAnswers, correctAnswer));
                }
                myReader3.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                myConnection.Close();
            }

            return questions;
        }

        /// <summary>
        /// Query the database and create a list of subjects
        /// </summary>
        /// <returns></returns>
        public List<string> getAllTestSubjects()
        {
            List<string> subjects = new List<string>();

            // Connect to local database.
            SqlConnection myConnection = new SqlConnection(this._dbConnectionString);


            // Get all the multiple choice questions from the database and add them to the assessment
            try
            {
                myConnection.Open();

                SqlDataReader myReader3 = null;
                SqlCommand myCommand3 = new SqlCommand("SELECT [QuestionSubjects].* FROM [QuestionSubjects] ", myConnection);
                myReader3 = myCommand3.ExecuteReader();
                while (myReader3.Read())
                {
                    string text = myReader3["Name"].ToString();
                    subjects.Add(text);
                }
                myReader3.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                myConnection.Close();
            }

            return subjects;
        }

        /// <summary>
        /// add the provided assessment with the supplied subject to the database
        /// </summary>
        /// <param name="subject"></param>
        /// <param name="assessment"></param>
        public void addAssessmentToDatabase(String subject, Assessment assessment)
        {
            // connect to local database
            SqlConnection myConnection = new SqlConnection(this._dbConnectionString);

            try
            {
                // Serialize the assessment for storage in the database
                MemoryStream memoryStream = new MemoryStream();
                BinaryFormatter binaryFormatter = new BinaryFormatter();
                binaryFormatter.Serialize(memoryStream, assessment);
                string serializedAssessment = Convert.ToBase64String(memoryStream.ToArray());

                myConnection.Open();

                int assessmentDatabaseId = -1;
                // Insert into the database
                SqlDataReader myReader4 = null;
                SqlCommand myCommand4 =
                    new SqlCommand(
                        "INSERT INTO Assessments (Subject, Title, Date, SerializedAssessment) " +
                        "VALUES ('" + subject + "', '" + assessment.Name + "', '" + assessment.CreatedDate + "', '" + serializedAssessment + "') SELECT @@IDENTITY AS AssessmentId", myConnection);
                myReader4 = myCommand4.ExecuteReader();
                while (myReader4.Read())
                {
                    assessmentDatabaseId = Int16.Parse(myReader4["AssessmentId"].ToString());
                }
                myReader4.Close();
                myConnection.Close();
            }
            catch (Exception)
            {
            }
            finally
            {
                myConnection.Close();
            }
        }

        /// <summary>
        /// Deserialize the assessment and add it to the database
        /// </summary>
        /// <param name="subject"></param>
        /// <param name="serialized_assessment"></param>
        public void addSerializedAssessmentToDatabase(string subject, string serialized_assessment)
        {
            // Deserialize the assessment
            Assessment assessment = null;
            byte[] assessmentStringBytes = Convert.FromBase64String(serialized_assessment);
            MemoryStream memoryStream = new MemoryStream(assessmentStringBytes);
            BinaryFormatter binaryFormatter = new BinaryFormatter();
            assessment = (Assessment)binaryFormatter.Deserialize(memoryStream);
            memoryStream.Close();

            this.addAssessmentToDatabase(subject, assessment);
        }

        public string calculateAssessmentGrade(string testName, string answers)
        {
            Assessment assessment = getAssessmentFromName(testName);
            string result = assessment.getGrade(answers);

            return result;
        }

        public string getCorrectAnswers(string testName)
        {
            Assessment assessment = getAssessmentFromName(testName);

            string correctAnswers = assessment.getCorrectAnswers();
            return correctAnswers;
        }
    }
}
